﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Web;
using System.IO;
using System.Data;
using System.Data.OleDb;

namespace AGuang.WebTool {
    /// <summary>
    /// 导出数据专用的类
    /// </summary>
    public class Export {
        /// <summary>
        /// 生成Excel文件 
        /// </summary>
        /// <param name="ds">创建时需要的dataset数据集</param>
        /// <param name="fileName">创建的excel文件名</param>
        public void CreateExcel(DataTable oDt, string fileName,System.Text.Encoding encoding) {
            StringBuilder strb = new StringBuilder();
            strb.Append(" <html xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
            strb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
            strb.Append("xmlns=\"http://www.w3.org/TR/REC-html40\"");
            strb.Append(" <head> <meta http-equiv='Content-Type' content='text/html; charset=" + encoding.WebName + "'>");
            strb.Append(" <style>");
            strb.Append(".xl26");
            strb.Append(" {mso-style-parent:style0;");
            strb.Append(" font-family:\"Times New Roman\", serif;");
            strb.Append(" mso-font-charset:0;");
            strb.Append(" mso-number-format:\"@\";}");
            strb.Append(" </style>");
            strb.Append(" <xml>");
            strb.Append(" <x:ExcelWorkbook>");
            strb.Append("  <x:ExcelWorksheets>");
            strb.Append("  <x:ExcelWorksheet>");
            strb.Append("    <x:Name>Sheet1 </x:Name>");
            strb.Append("    <x:WorksheetOptions>");
            strb.Append("    <x:DefaultRowHeight>285 </x:DefaultRowHeight>");
            strb.Append("    <x:Selected/>");
            strb.Append("    <x:Panes>");
            strb.Append("      <x:Pane>");
            strb.Append("      <x:Number>3 </x:Number>");
            strb.Append("      <x:ActiveCol>1 </x:ActiveCol>");
            strb.Append("      </x:Pane>");
            strb.Append("    </x:Panes>");
            strb.Append("    <x:ProtectContents>False</x:ProtectContents>");
            strb.Append("    <x:ProtectObjects>False</x:ProtectObjects>");
            strb.Append("    <x:ProtectScenarios>False</x:ProtectScenarios>");
            strb.Append("    </x:WorksheetOptions>");
            strb.Append("   </x:ExcelWorksheet>");
            strb.Append("   <x:WindowHeight>6750 </x:WindowHeight>");
           strb.Append("    <x:WindowWidth>10620 </x:WindowWidth>");
           strb.Append("    <x:WindowTopX>480 </x:WindowTopX>");
           strb.Append("    <x:WindowTopY>75 </x:WindowTopY>");
           strb.Append("    <x:ProtectStructure>False</x:ProtectStructure>");
           strb.Append("    <x:ProtectWindows>False</x:ProtectWindows>");
           strb.Append("   </x:ExcelWorksheets>");
            strb.Append(" <x:WindowHeight>6750 </x:WindowHeight>");
            strb.Append(" <x:WindowWidth>10620 </x:WindowWidth>");
            strb.Append(" <x:WindowTopX>480 </x:WindowTopX>");
            strb.Append(" <x:WindowTopY>75 </x:WindowTopY>");
            strb.Append(" <x:ProtectStructure>False</x:ProtectStructure>");
            strb.Append(" <x:ProtectWindows>False</x:ProtectWindows>");
            strb.Append(" </x:ExcelWorkbook>");
            strb.Append(" </xml>");
            strb.Append("");
            
            strb.Append(" </head> <body> <table align=\"center\" style='border-collapse:collapse;table-layout:fixed'> <tr>");
            if (oDt.Rows.Count > 0) {
                //写列标题    
                int columncount = oDt.Columns.Count;
                for (int columi = 0; columi < columncount; columi++) {
                    strb.Append(" <td> <b>" + oDt.Columns[columi] + " </b> </td>");
                }
                strb.Append(" </tr>");
                //写数据  
                for (int i = 0; i < oDt.Rows.Count; i++) {
                    strb.Append(" <tr>");
                    for (int j = 0; j < oDt.Columns.Count; j++) {
                        strb.Append(" <td class='xl26'>" + oDt.Rows[i][j].ToString() + " </td>");
                    }
                    strb.Append(" </tr>");
                }
            }
            strb.Append(" </body> </html>");
            
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = false;
            HttpContext.Current.Response.Charset = encoding.WebName;
            HttpContext.Current.Response.ContentEncoding = encoding;//设置输出流为简体中文  
            HttpContext.Current.Response.HeaderEncoding = encoding;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName));
            HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。    
            HttpContext.Current.Response.Write(strb);
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// 生成Excel文件
        /// </summary>
        /// <param name="ds">创建时需要的dataset数据集</param>
        /// <param name="fileName">创建的excel文件名</param>
        public void CreateExcel(DataTable oDt, string fileName) {
            CreateExcel(oDt, fileName, HttpContext.Current.Response.HeaderEncoding);
            //CreateExcel(oDt, fileName, System.Text.Encoding.GetEncoding("GB2312"));
        }

        /// <summary>
        /// 将Excel表格导成DataSet
        /// </summary>
        /// <param name="path">Excel的路径</param>
        /// <param name="hasHead">是否有表头</param>
        /// <returns>Excel中所有的数据</returns>
        public DataSet ImportExcel(String path, bool hasHead) {
            DataSet ds = new DataSet();
            if (!File.Exists(path)) throw new FileNotFoundException("文件不存在", path);
            //String hdr = ";HDR=no;IMEX=1";
            String connStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=no;IMEX=1\";", path);

                OleDbConnection conn = new OleDbConnection(connStr);
                conn.Open();
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow dr in dt.Rows) {
                    String table = dr[2].ToString();
                    OleDbDataAdapter adaper = new OleDbDataAdapter(String.Format("select * from [{0}]", table), conn);
                    adaper.Fill(ds);
                }
                conn.Close();

            return ds;
        }

    }
}
